Public Class frmImportarMateriales

    Private Sub frmImportarMateriales_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        lblDescLayout.BackColor = Color.White
        lblLayout.BackColor = Color.White
        lblPreparar.BackColor = Color.White
        lblSeleccione.BackColor = Color.White
        lblStatus.BackColor = Color.White
        lblTitulo.BackColor = Color.White

        lblDescLayout.ForeColor = System.Drawing.Color.FromArgb(64, 64, 64)
        lblLayout.ForeColor = System.Drawing.Color.FromArgb(64, 64, 64)
        lblPreparar.ForeColor = System.Drawing.Color.FromArgb(64, 64, 64)
        lblSeleccione.ForeColor = System.Drawing.Color.FromArgb(64, 64, 64)
        lblStatus.ForeColor = System.Drawing.Color.FromArgb(64, 64, 64)
        lblTitulo.ForeColor = System.Drawing.Color.FromArgb(64, 64, 64)

        txtFichero.BackColor = Color.White
        txtFichero.ForeColor = System.Drawing.Color.FromArgb(64, 64, 64)
       
        panelSuperior.Style.BackColor1.Color = Color.White
        panelSuperior.Style.BackColor1.Color = Color.White
        panelSuperior.Style.BorderColor.Color = Color.LightGray

        panelInferior.Style.BackColor1.Color = Color.White
        panelInferior.Style.BackColor2.Color = Color.Silver
        panelInferior.Style.BorderColor.Color = Color.LightGray

        Line3.BackColor = Color.White
        Line3.ForeColor = System.Drawing.Color.FromArgb(224, 224, 224)

        txtFichero.Clear()

        lblStatus.Text = "Esperando archivo..."
        btnExaminar.Focus()
    End Sub

    Private Sub btnCancelar_Click(sender As System.Object, e As System.EventArgs) Handles btnCancelar.Click
        lblStatus.Text = "Esperando archivo..."
        txtFichero.Border.BackColor = Color.White
        txtFichero.Border.BackColor2 = Color.White
        txtFichero.Border.BorderColor = System.Drawing.Color.FromArgb(198, 195, 198)
        txtFichero.Border.BorderColor2 = System.Drawing.Color.FromArgb(198, 195, 198)

        btnExaminar.Enabled = True

    End Sub

    Private Sub btnTodosRegresar_Click(sender As System.Object, e As System.EventArgs) Handles btnTodosRegresar.Click
        Me.DialogResult = Windows.Forms.DialogResult.Cancel
        Close()

    End Sub

    Private Sub btnExaminar_Click(sender As System.Object, e As System.EventArgs) Handles btnExaminar.Click
        Dim oFD As New OpenFileDialog
        With oFD
            .Title = "Seleccionar fichero"
            .Filter = "Ficheros de texto (*.xls;*.xlsx)|*.xls;*.xlsx" & _
                      "|Todos los ficheros (*.*)|*.*"
            .FileName = Me.txtFichero.Text
            If .ShowDialog = System.Windows.Forms.DialogResult.OK Then
                Me.txtFichero.Text = .FileName
            End If
        End With

        txtFichero.Border.BackColor = Color.White
        txtFichero.Border.BackColor2 = Color.White
        txtFichero.Border.BorderColor = Color.White
        txtFichero.Border.BorderColor2 = Color.White

        btnExaminar.Enabled = False
        lblStatus.Text = "Preparado para importar..."

    End Sub

    Private Sub btnGuardar_Click(sender As System.Object, e As System.EventArgs) Handles btnGuardar.Click
        btnTodosRegresar.Enabled = False
        btnCancelar.Enabled = False
        btnGuardar.Enabled = False
        IMPORTAR()

    End Sub
    Private Sub IMPORTAR()

        Dim xl As Object
        Dim wb As Object

        'Crear objeto excel
        xl = CreateObject("Excel.Application")
        xl.Visible = False
        wb = xl.activeworkbook
        wb = xl.Workbooks.Open _
        (txtFichero.Text, True, True, , "")

        Dim rng As Integer = 1
        Dim col As Integer = 1
        Dim i As Integer = 1

        Dim CODIGO As String = ""
        Dim NOMBRE As String = ""
        Dim ADMINISTRADOR As String = ""
        Dim ACTIVO As Integer = 0
        Dim DESTINO As String = ""
        Dim ESTADO As String = ""
        Dim ALMACEN As String = ""
        Dim UBICACION As String = ""
        Dim CLASIFICACION1 As String = ""
        Dim CLASIFICACION2 As String = ""
        Dim CLASIFICACION3 As String = ""
        Dim DIAS As Integer = 0
        Dim PRECIO As Double = 0
        Dim MONEDA As String = 0
        Dim TIPO As String = ""
        Dim ORIGEN As String = ""
        Dim UNIDAD1 As String = ""
        Dim UNIDAD2 As String = ""
        Dim CODE_COLOR As String = ""
        Dim COLOR As String = ""
        Dim LARGO As Double = 0
        Dim ALTO As Double = 0
        Dim ANCHO As Double = 0
        Dim VOLUMEN As Double = 0

        For i = 2 To 484
            CODIGO = wb.sheets(1).cells(i, 1).value
            NOMBRE = wb.sheets(1).cells(i, 3).value
            ADMINISTRADOR = wb.sheets(1).cells(i, 4).value
            ACTIVO = CInt(wb.sheets(1).cells(i, 5).value)
            DESTINO = wb.sheets(1).cells(i, 6).value
            ESTADO = wb.sheets(1).cells(i, 7).value
            ALMACEN = wb.sheets(1).cells(i, 8).value
            UBICACION = wb.sheets(1).cells(i, 9).value
            CLASIFICACION1 = wb.sheets(1).cells(i, 10).value
            CLASIFICACION2 = wb.sheets(1).cells(i, 11).value
            CLASIFICACION3 = wb.sheets(1).cells(i, 12).value
            DIAS = CInt(wb.sheets(1).cells(i, 14).value)
            PRECIO = CDbl(wb.sheets(1).cells(i, 15).value)
            MONEDA = wb.sheets(1).cells(i, 16).value
            TIPO = wb.sheets(1).cells(i, 17).value
            ORIGEN = wb.sheets(1).cells(i, 18).value
            UNIDAD1 = BuscarStrDatoCitra("SELECT NOMBRE FROM UNIDADES_MEDICION WHERE ABREVIACION='" & wb.sheets(1).cells(i, 19).value & "'")
            UNIDAD2 = BuscarStrDatoCitra("SELECT NOMBRE FROM UNIDADES_MEDICION WHERE ABREVIACION='" & wb.sheets(1).cells(i, 20).value & "'")
            CODE_COLOR = wb.sheets(1).cells(i, 29).value
            COLOR = wb.sheets(1).cells(i, 30).value
            ALTO = CDbl(wb.sheets(1).cells(i, 24).value)
            ANCHO = CDbl(wb.sheets(1).cells(i, 25).value)
            LARGO = CDbl(wb.sheets(1).cells(i, 26).value)
            VOLUMEN = CDbl(wb.sheets(1).cells(i, 27).value)

            Dim IDCLASI As Integer = BuscarIntDatoCitra("SELECT ID FROM CLASIFICACIONES WHERE CODIGO='" & CLASIFICACION2 & "'")
            If IDCLASI = 0 Then
                Dim CLASIFICACIONES As New clsCLASIFICACIONES
                Dim RESULTADO As Integer = CLASIFICACIONES.GUARDAR_CLASIFICACION(CATALOGOS_IDS.Catalogo_Materiales, CLASIFICACIONES_MATERIALES.Clasificacion_2, CLASIFICACION2, CLASIFICACION2, 1)
            End If

            Dim IDCOLOR As Integer = BuscarIntDatoInicial("SELECT IDCOLOR FROM COLORES WHERE CODIGO='" & CODE_COLOR & "'")
            If IDCOLOR = 0 Then

                Dim COLORES As New clsCOLORES
                Dim RESULTADO As Integer = COLORES.GUARDA_COLOR(CODE_COLOR, COLOR, 1)
            End If

            Guardar(CODIGO, NOMBRE, ACTIVO, PRECIO, COLOR, VOLUMEN, LARGO, ALTO, ANCHO, CODE_COLOR, DIAS, TIPO, ORIGEN, UNIDAD1, UNIDAD2, DESTINO, ESTADO, ALMACEN, UBICACION, CLASIFICACION1, CLASIFICACION2, CLASIFICACION3, MONEDA, ADMINISTRADOR)
            lblStatus.Text = "Importando " & NOMBRE
            Application.DoEvents()
        Next

        'cerramos el archivo Excel
        wb.Close(SaveChanges:=False)
        xl.Quit()

        Me.DialogResult = Windows.Forms.DialogResult.OK
        Close()

    End Sub
    Sub Guardar(ByVal CODIGO As String, ByVal NOMBRE As String, ByVal ESTATUS As Integer, ByVal PRECIO As Double, ByVal COLOR As String, ByVal VOLUMEN As Double, ByVal LARGO As Double, ByVal ALTO As Double, ByVal ANCHO As Double, ByVal CODIGO_COLOR As String, ByVal DIAS As Integer, ByVal TIPO_MATERIAL As String, ByVal ORIGEN_MATERIAL As String, ByVal UNIDAD1 As String, ByVal UNIDAD2 As String, ByVal DESTINO As String, ByVal ESTADO_PRODUCTO As String, ByVal ALMACEN As String, ByVal UBICACION As String, ByVal CLASIF1 As String, ByVal CLASIF2 As String, ByVal CLASIF3 As String, ByVal MONEDA As String, ByVal ADMINISTRADOR As String)
        Dim MATERIAL As New clsMATERIALES

        Dim TIPO As Integer = 0
        If DESTINO = "CONSUMIBLE" Then
            TIPO = 1
        Else
            TIPO = 0
        End If

        Dim ESTADO As Integer = 0
        If ESTADO_PRODUCTO = "NORMAL" Then
            ESTADO = 1
        Else
            ESTADO = 0
        End If

        If Len(CLASIF1) = 0 Then
            CLASIF1 = " "
        End If

        If Len(CLASIF2) = 0 Then
            CLASIF2 = " "
        End If

        If Len(CLASIF3) = 0 Then
            CLASIF3 = " "
        End If

        If Len(ADMINISTRADOR) = 0 Then
            ADMINISTRADOR = " "
        End If

        Dim TIPOS As Integer = 0
        Dim ORIGEN As Integer = 0

        If TIPO_MATERIAL = "RESURTIBLE" Then
            TIPOS = 1
        Else
            TIPOS = 0
        End If

        If ORIGEN_MATERIAL = "NACIONAL" Then
            ORIGEN = 1
        Else
            ORIGEN = 0
        End If

        Dim resultado As Integer = MATERIAL.INSERTA_MATERIAL(CODIGO, NOMBRE, " ", Nothing, ESTATUS, UNIDAD1, CLASIF1, CLASIF2, CLASIF3, MONEDA, " ", DIAS, " ", COLOR, "", TIPO, PRECIO, UNIDAD2, 0, 0, ESTADO, ADMINISTRADOR, VOLUMEN, 0, 0, 0, LARGO, ALTO, ANCHO, " ", UBICACION, TIPOS, ORIGEN, ALMACEN)

    End Sub
  
End Class